use serde::Serialize;

use crate::{
    error::{ec, new_err_msg, AppErr, SqlxErr},
    store::{adv, get_pool},
};

use super::Conn;

pub async fn create(
    device_id: u64,
    adv_id: u64,
    position: u8,
    play_start: u32,
    play_end: u32,
    duration: u32,
) -> Result<u64, AppErr> {
    let mut tx = get_pool().begin().await?;

    if false == adv::is_exists(&mut *tx, adv_id).await? {
        return Err(new_err_msg(ec::ADV_NOT_EXISTS, ec::ADV_NOT_EXISTS_MSG));
    }

    let ret = sqlx::query!(
        r#"
        INSERT INTO 
        tb_adv_cfg (device_id, adv_id, position, play_start, play_end, duration) 
        VALUES (?, ?, ?, ?, ?, ?)
    "#,
        device_id,
        adv_id,
        position,
        play_start,
        play_end,
        duration
    )
    .execute(&mut *tx)
    .await;

    match ret {
        Ok(ret) => {
            tx.commit().await?;
            Ok(ret.last_insert_id())
        }
        Err(SqlxErr::Database(err)) if err.is_unique_violation() => Err(new_err_msg(
            ec::ADV_SET_IS_EXISTS,
            ec::ADV_SET_IS_EXISTS_MSG,
        )),
        Err(e) => Err(e.into()),
    }
}

#[derive(Debug, Serialize)]
pub struct TbAdvCfg {
    id: u64,
    name: String,
    adv_id: u64,
    position: u8,
    play_start: u32,
    play_end: u32,
    adv_type: u8,
    duration: u32,
}

pub async fn query(device_id: u64) -> Result<Box<[TbAdvCfg]>, SqlxErr> {

    let res = sqlx::query_as!(
        TbAdvCfg,
        r#"SELECT 
        tb_adv_cfg.id, tb_adv.name, tb_adv_cfg.adv_id, tb_adv_cfg.position, 
        tb_adv_cfg.play_start, tb_adv_cfg.play_end, tb_adv.adv_type, tb_adv_cfg.duration 
        FROM tb_adv_cfg 
        JOIN tb_adv 
        ON tb_adv_cfg.adv_id = tb_adv.id 
        WHERE tb_adv_cfg.device_id = ?
        "#,
        device_id
    ).fetch_all(get_pool()).await?;

    Ok(res.into_boxed_slice())
}

pub async fn delete(id: u64) -> Result<(), SqlxErr> {
    sqlx::query!("DELETE FROM tb_adv_cfg WHERE id = ?", id).execute(get_pool()).await?;
    Ok(())
}

pub async fn delete_for_adv(conn: &mut Conn, adv_id: u64) -> Result<(), SqlxErr> {
    sqlx::query!("DELETE FROM tb_adv_cfg WHERE adv_id = ?", adv_id).execute(conn).await?;
    Ok(())
}

pub async fn delete_for_device(conn: &mut Conn, device_id: u64) -> Result<(), SqlxErr> {
    sqlx::query!("DELETE FROM tb_adv_cfg WHERE device_id = ?", device_id).execute(conn).await?;
    Ok(())
}
